﻿Imports System.Data.OleDb
Imports System.Text
Imports System.Security.Cryptography

Module XL_NguoiDung
    Structure NguoiDung
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _hoTen As String
        Property HoTen() As String
            Get
                Return _hoTen
            End Get
            Set(ByVal value As String)
                _hoTen = value
            End Set
        End Property
        Private _username As String
        Property Username() As String
            Get
                Return _username
            End Get
            Set(ByVal value As String)
                _username = value
            End Set
        End Property
        Private _matKhau As String
        Property MatKhau() As String
            Get
                Return _matKhau
            End Get
            Set(ByVal value As String)
                _matKhau = value
            End Set
        End Property
        Private _loaind As Integer
        Property LoaiND() As Integer
            Get
                Return _loaind
            End Get
            Set(ByVal value As Integer)
                _loaind = value
            End Set
        End Property
        Private _trangthai As Integer
        Property TrangThai() As Integer
            Get
                Return _trangthai
            End Get
            Set(ByVal value As Integer)
                _trangthai = value
            End Set
        End Property

    End Structure

    Public Function LayDS() As List(Of NguoiDung)
        Try
            Dim ds As New List(Of NguoiDung)

            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from NguoiDung where TrangThai=@tt", con)
            cmd.Parameters.AddWithValue("@tt", 1)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As NguoiDung = New NguoiDung()
                p.ID = rd.GetInt32(0)
                p.HoTen = rd.GetString(1)
                p.Username = rd.GetString(2)
                p.MatKhau = rd.GetString(3)
                p.LoaiND = rd.GetInt32(4)
                p.TrangThai = rd.GetInt32(5)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As NguoiDung
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from NguoiDung where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As NguoiDung = Nothing
            If rd.Read() Then
                p = New NguoiDung()
                p.ID = rd.GetInt32(0)
                p.HoTen = rd.GetString(1)
                p.Username = rd.GetString(2)
                p.MatKhau = rd.GetString(3)
                p.LoaiND = rd.GetInt32(4)
                p.TrangThai = rd.GetInt32(5)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function MD5(ByVal chuoi_nguon As String) As String
        'Tạo đối tượng mã hóa dạng ASCII để mã hóa chuỗi ký tự
        Dim ASCIIenc As New ASCIIEncoding
        Dim strReturn As String = ""
        'Lấy mảng byte của nguồn văn bản
        Dim ByteSourceText() As Byte = ASCIIenc.GetBytes(chuoi_nguon)
        'Cài đặt chuẩn mã hóa MD5
        Dim Md5Hash As New MD5CryptoServiceProvider
        'Tính giá trị bảng mã của nguồn văn bản
        Dim ByteHash() As Byte = Md5Hash.ComputeHash(ByteSourceText)
        'Tạo vòng lặp để chuyển đổi bảng mã sang một chuỗi văn bản
        For Each b As Byte In ByteHash
            'Chuyển các byte sang dạng chuỗi
            strReturn &= b.ToString("x2")
        Next
        'Trả lại hàm
        Return strReturn

    End Function


    Public Function KiemTraUsername(ByVal username As String) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from NguoiDung where TenDN=@username", con)
            cmd.Parameters.AddWithValue("@username", username)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            If rd.Read() Then
                Return 1
            Else
                Return 0
            End If
            con.Close()
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function Them(ByRef p As NguoiDung) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into NguoiDung(HoTen, TenDN, MatKhau, LoaiND, TrangThai) values(@HoTen, @Username, @MatKhau, @loai, @tt)", con)
            cmd.Parameters.AddWithValue("@HoTen", p.HoTen)
            cmd.Parameters.AddWithValue("@TenDN", p.Username)
            cmd.Parameters.AddWithValue("@MatKhau", p.MatKhau)
            cmd.Parameters.AddWithValue("@loai", p.LoaiND)
            cmd.Parameters.AddWithValue("@tt", p.TrangThai)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@Identity"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal ten As String) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update NguoiDung set TrangThai = @tt where TenDN = @ten", con)
            cmd.Parameters.AddWithValue("@tt", 0)
            cmd.Parameters.AddWithValue("@ten", ten)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function KiemTraDangNhap(ByVal username As String, ByVal pass As String, ByRef mand As Integer) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from NguoiDung where TenDN=@username and MatKhau=@matkhau", con)
            cmd.Parameters.AddWithValue("@username", username)
            cmd.Parameters.AddWithValue("@matkhau", pass)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            If rd.Read() Then
                mand = rd.GetInt32(0)
                Dim loaind As Integer = rd.GetInt32(4)
                If loaind = 0 Then
                    Return 1
                Else
                    Return 2
                End If
            Else
                Return 0
            End If
            con.Close()
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function DoiMatKhau(ByVal ten As String, ByVal pass As String) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update NguoiDung set MatKhau=@matkhau where TenDN=@ten", con)
            cmd.Parameters.AddWithValue("@matkhau", pass)
            cmd.Parameters.AddWithValue("@username", ten)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function


End Module
